package view;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ComplaintCostReportDAO {
	
	private DBConnect con = null;
        
    public ComplaintCostReportDAO (DBConnect incon)
    {
        con = incon;
        try
        {
            con.getConnection().setAutoCommit(false);
        }
        catch (SQLException e)
        {
        	controller.ErrorHandle.LogError(e);
        }
    }
    
    public String GetReportString()
    {
    	PreparedStatement ps= null;
        ResultSet rs=null;
        String Output = "";
        
        
        try
        {
          ps=con.getConnection().prepareStatement(
        		  "with complaints_by_product as ( "+//get complaints per product
        			"select ol.product_id, count(cc.complaint_cd) as problem "+
        			"from "+con.getSchema()+".cust_complaint cc "+
        			"inner join "+con.getSchema()+".order_line ol on cc.order_id = ol.order_id "+
        			"group by ol.product_id "+
        			"), "+
        			"units_sold as ( "+//get number of products sold for each
        			"select ol.product_id, sum(ol.Ordered_quantity) as sold "+
        			"from "+con.getSchema()+".factory_order fo "+
        			"inner join "+con.getSchema()+".order_line ol on ol.order_id = fo.order_id "+
        			"inner join "+con.getSchema()+".product p on p.product_id = ol.product_id "+
        			"group by ol.product_id "+
        			"),"+
        			"prod_pricing as ( "+ //get the cost of each product and its materials cost
        			"select p.product_id, p.product_description, p.product_standard_price, to_char(round(sum(u.goes_into_quantity*r.material_standard_cost),2),9999.99) as materials_cost "+
        			"from "+con.getSchema()+".Product p "+
        			"inner join "+con.getSchema()+".uses u on u.product_id = p.product_id "+
        			"inner join "+con.getSchema()+".Raw_material r on r.material_id = u.material_id "+
        			"group by p.product_id, p.product_description, p.product_standard_price "+
        			"order by p.product_id "+
        			") "+
        			"select distinct pp.product_description, to_char(pp.product_standard_price, '$9999.99'), to_char(pp.materials_cost, '$9999.99'), to_char((Round((cp.problem / us.sold),4)*100),99.99) as ComplaintVsSold "+ //format data for output
        			"from "+con.getSchema()+".cust_complaint cc "+
        			"inner Join "+con.getSchema()+".order_line ol on ol.order_id = cc.order_id "+
        			"inner join prod_pricing pp on pp.product_id = ol.product_id "+
        			"inner join complaints_by_product cp on cp.product_id = ol.product_id "+
        			"inner join units_sold us on us.product_id = ol.product_id "+
        			"where cc.Complaint_date between add_months(TRUNC(SYSDATE, 'YYYY'),-12) and Trunc(SYSDATE, 'YYYY')"
        		  );
          
          	final long startTime = System.nanoTime();
          	rs=ps.executeQuery();
            while(rs.next())
            {
            	Output +=( rs.getString(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3) + "\t" + rs.getString(4) + "\r\n");      
            }
            final long duration = System.nanoTime() - startTime;
            
            System.out.println("Display Complete, took: " + (duration/ 1000000000.0) + " seconds");
          rs.close();
          ps.close();
        }
        catch(SQLException e)
        {
        	controller.ErrorHandle.LogError(e,"SQL Error in Report 2");
        }
        catch(Exception e)
        {
        	controller.ErrorHandle.LogError(e,"Unknown Error in Report 2");
        }
    	return Output;
    }
	/*
	Of the complaints we received last year, what products were involved, their price, their material price, and the percentage of complaints vs units sold.
	

	with complaints_by_product as
	(
	select ol.product_id, count(cc.complaint_cd) as problem
	from CSC342.cust_complaint cc
	inner join CSC342.order_line ol on cc.order_id = ol.order_id
	group by ol.product_id
	),
	units_sold as
	(
	select ol.product_id, sum(ol.Ordered_quantity) as sold
	from CSC342.factory_order fo
	inner join CSC342.order_line ol on ol.order_id = fo.order_id
	inner join CSC342.product p on p.product_id = ol.product_id
	group by ol.product_id
	),
	prod_pricing as
	(
	select p.product_id, p.product_description, p.product_standard_price, to_char(round(sum(u.goes_into_quantity*r.material_standard_cost),2),9999.99) as materials_cost
	from CSC342.Product p
	inner join CSC342.uses u on u.product_id = p.product_id
	inner join CSC342.Raw_material r on r.material_id = u.material_id
	group by p.product_id, p.product_description, p.product_standard_price
	order by p.product_id
	)
	select distinct pp.product_description, pp.product_standard_price, pp.materials_cost, Round((cp.problem / us.sold), 4) as ComplaintVsSold
	from CSC342.cust_complaint cc
	inner Join CSC342.order_line ol on ol.order_id = cc.order_id
	inner join prod_pricing pp on pp.product_id = ol.product_id
	inner join complaints_by_product cp on cp.product_id = ol.product_id
	inner join units_sold us on us.product_id = ol.product_id
	where cc.Complaint_date between add_months(TRUNC(SYSDATE, 'YYYY'),-12) and Trunc(SYSDATE, 'YYYY');

	
	PRODUCT_DESCRIPTION                                PRODUCT_STANDARD_PRICE MATERIALS_COST COMPLAINTVSSOLD
	-------------------------------------------------- ---------------------- -------------- ---------------
	American Modern 6 Ft Dining Room Table                                675   312.28                0.0021 
	European Modern 6 Ft Dining Room Table                                675   305.28                0.0016 
	Country King Sized Bed                                                700   213.28                0.0016 
	Continental Cherry 6 Ft Dining Room Table                             575   273.38                0.0018 
	Contemporary 8 Ft Dining Room Table                                   775   384.28                0.0019 
	Contemporary King Sized Bed                                           800   384.28                0.0014 
	Country 8 Ft Dining Room Table                                        575   194.30                0.0016 
	European Modern King Sized Bed                                        800   425.28                0.0017 
	Early American King Sized Bed                                         750   213.28                0.0017 

	 9 rows selected 

	Elapsed: 00:00:00.359
	*/

}
